package com.atsugon.demo02.mapper;

import com.atsugon.demo02.bean.Alert;
import com.atsugon.demo02.bean.AlertCount;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface AlertMapper extends BaseMapper<Alert> {

    //获取某天的报警次数
    @Select({
            "select COUNT(id) AS NUM ",
            "from do_alert",
            "where DATE(create_time)=#{date}",
    })
    Integer DayselectByDate(String date);

    //获取某周的报警次数
    @Select({
            "select COUNT(id) AS NUM ",
            "from do_alert",
            "where DATE_FORMAT(create_time,'%Y-%u') = DATE_FORMAT(#{date},'%Y-%u')",
    })
    Integer WeekselectByDate(String date);

    //获取某月的报警次数
    @Select({
            "select COUNT(id) AS NUM ",
            "from do_alert",
            "where DATE_FORMAT(create_time,'%Y-%M') = DATE_FORMAT(#{date},'%Y-%M')",
    })
    Integer MonthselectByDate(String date);

    //获取某年的报警次数
    @Select({
            "select COUNT(id) AS NUM ",
            "from do_alert",
            "where DATE_FORMAT(create_time,'%Y') = DATE_FORMAT(#{date},'%Y')",
    })
    Integer YearselectByDate(String date);


    //增加数据 涉及列名表明时只能使用${tablename}方式导致无法防御注入攻击，建议手动写入
    @Insert({
            "INSERT INTO do_alert (id,${rowname},priority,content,status,times,create_time)",
            "VALUES (#{id},'null',123,'null',0,789,'2022-10-18 16:59:57')",
    })
    Integer InsertById(String rowname,Integer id);

    //删除数据
    @Delete({
            "DELETE from do_alert",
            "WHERE id=#{id}"
    })
    Integer DeleteById(Integer id);

    //修改数据
    @Update({
            "UPDATE do_alert",
            "SET status=#{status}",
            "WHERE id=#{id}"
    })
    Integer UpdateStatus(Integer id,Integer status);

    //按天获取报警次数
    List<AlertCount> xmlDayselectByDate();
    List<AlertCount> xmlDayselectByDateLimitPage(@Param("pfrom") Long pfrom,
                                                 @Param("pto") Long pto);
    //按周获取报警次数
    List<AlertCount> xmlDayselectByWeek();
    List<AlertCount> xmlDayselectByWeekLimitPage(@Param("pfrom") Long pfrom,
                                                 @Param("pto") Long pto);
    //按月获取报警次数
    List<AlertCount> xmlDayselectByMonth();
    List<AlertCount> xmlDayselectByMonthLimitPage(@Param("pfrom") Long pfrom,
                                                 @Param("pto") Long pto);
    //按年获取报警次数
    List<AlertCount> xmlDayselectByYear();
    List<AlertCount> xmlDayselectByYearLimitPage(@Param("pfrom") Long pfrom,
                                                 @Param("pto") Long pto);



}

